/**************************************************************
** Finds the version of the content that is published.
**
**
***************************************************************
*/


CREATE    PROCEDURE amsp_CMGetPublishedContentID
    @InContentID numeric,
    @OutContentID numeric OUTPUT,
    @InFuseFlag Numeric = 0
AS
BEGIN
  DECLARE
    @MyWorkflowStatusCode char(1),
    @ParentWorkflowStatusCode char(1),
    @ChildWorkflowStatusCode char(1),
    @ChildContentID numeric,
    @ParentContentID numeric,
    @Counter integer
    
  -- Initialize our return code
  SET @OutContentID = 0
   
  -- First, figure out if this one is published, or there is one that is published associated
  -- with this one.
  SELECT @MyWorkflowStatusCode = a.WorkflowStatusCode,
         @ParentContentID = a.PreviousContentID,
         @ParentWorkflowStatusCode = c.WorkflowStatusCode,
         @ChildContentID = b.ContentID,
         @ChildWorkflowStatusCode = b.WorkflowStatusCode
    FROM (Content a LEFT OUTER JOIN Content b ON a.ContentID = b.PreviousContentID)
         LEFT OUTER JOIN Content c ON a.PreviousContentID = c.ContentID
   WHERE a.ContentID = @InContentID
   
  -- If the status of the contentID sent in is P for Published, just return it
  -- IK: Check for @InFuseFlag to handle "Approved" status for fusing.
  IF @MyWorkflowStatusCode = 'P' OR (@InFuseFlag = '1' AND @MyWorkflowStatusCode = 'A')
    SET @OutContentID = @InContentID
  ELSE
    BEGIN
      -- Otherwise, if the status of the Parent is P for Published, return the Parent's ID
      -- this should occur if the current one is working
      IF @ParentWorkflowStatusCode = 'P'
        SET @OutContentID = @ParentContentID
      ELSE
        BEGIN
          -- Otherwise, if there is a child, check it's status
          IF @ChildWorkflowStatusCode = 'P'
            SET @OutContentID = @ChildContentID
          ELSE BEGIN
            -- Otherwise, if there is a child, it must have or need a child, so loop
            -- To make sure a bad content list doesn't lock the server, we also
            -- limit this to 50 iterations.
            SET @Counter = 0
              
            WHILE (@ChildWorkflowStatusCode IS NOT NULL AND @OutContentID = 0 AND @Counter < 50) BEGIN
              SET @Counter = @Counter + 1
              SELECT @ChildWorkflowStatusCode = WorkflowStatusCode,
                     @ChildContentID = ContentID
                FROM Content
               WHERE PreviousContentID = @ChildContentID
               
              IF (@@RowCount = 1) BEGIN
                IF (@ChildWorkflowStatusCode = 'P') BEGIN
                  SET @OutContentID = @ChildContentID
                END
              END
            END
          END
        END
    END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetPublishedContentID] TO [IMIS]
GO